const query = require("./mysql");
const creatTable = `drop table if exists user;
CREATE TABLE user (
    id int(16) NOT NULL AUTO_INCREMENT,
    userName varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
    userPhone bigint NULL DEFAULT NULL,
    phoneCode bigint NULL DEFAULT NULL,
    token varchar(255) NULL DEFAULT NULL,
    loginTime datetime NULL DEFAULT NULL,
    PRIMARY KEY (id)
  )`;
let xlsx = require("xlsx");

let workbook = xlsx.readFile("./xlsx/user.xlsx"); //workbook就是xls文档对象

let sheetNames = workbook.SheetNames; //获取表明

let sheet = workbook.Sheets[sheetNames[0]]; //通过表明得到表对象

var data = xlsx.utils.sheet_to_json(sheet); //通过工具将表对象的数据读出来并转成json
async function writSql(data) {
  try {
    await query(creatTable);
    let inset = "INSERT INTO user ( userName, userPhone ) VALUES ";
    data.forEach((el, index) => {
      const phone = Number(el.手机.replace(/\s/gi, ""));
      if (isNaN(phone)) throw new Error(`第${index}的手机号有误`);
      inset += `( '${el.名字}', ${phone}),`;
    });
    inset = inset.substr(0, inset.length - 1) + ";";
    await query(inset);
    console.log("数据写入完成");
    process.exit();
  } catch (err) {
    console.log(err);
  }
}
writSql(data);
